--SELECT * FROM FN_LOAD_FORECAST_ENTRY_DETAILS ( 3, 7, 2010, 12 ) ORDER BY YEAR, MONTH
/**
	AUTHOR		: SUJAN
	DATE		: 13th JULY 2005
	DESCRIPTION	: THIS FUNCTION IS USED TO GET THE DETAILS OF THE LINE ITEMS TO BE DISPLAYED FOR THE SALES PERSON IN FORECAST ENTRY SCREEN
*/
ALTER  FUNCTION FN_LOAD_FORECAST_ENTRY_DETAILS( 
	@SALESPERSON_ID INT, 
	@START_MONTH INT, 
	@START_YEAR INT, 
	@SHOW_NEXT INT 	--NUMBER OF MONTHS INCLUDING THE START MONTH
 )
   
   RETURNS @FN_RESULTS TABLE( 
	PROJECT_ID 	INT,
	PROJECT_NAME 	VARCHAR(100),
	CUSTOMER_ID	INT,
	CUSTOMER_NAME VARCHAR(100),
	ENDCUSTOMER_ID  INT,
	ENDCUSTOMER_NAME VARCHAR(100),
	PRODUCT_ID	INT,
	PRODUCT_NAME	VARCHAR(50),
	MKTG_PART_NUMBER VARCHAR(50),
	ROW_ID		INT,
	MONTH		INT,
	YEAR		INT,
	QTY		INT,
	PRICE		FLOAT,
	CONFIDENCE_LEVEL FLOAT,
	SALESPERSON_NAME VARCHAR(50),
	LEVEL_ORDER	INT,
	RUNNING_TOTAL 	FLOAT,
	USER_DEFINED1 VARCHAR(50),
	USER_DEFINED2 VARCHAR(50),
	USER_DEFINED3 VARCHAR(50),
	USER_DEFINED4 VARCHAR(50),
	ENDUSER_ID  INT,
	ENDUSER_NAME VARCHAR(100)

	)
 AS
 BEGIN
	DECLARE @MONTH_COUNT 	INT
	DECLARE @PROJECT_ID 	INT
	DECLARE @PROJECT_NAME 	VARCHAR(100)
	DECLARE @CUSTOMER_ID	INT
	DECLARE @CUSTOMER_NAME	VARCHAR(100)
	DECLARE @ENDCUSTOMER_ID INT
	DECLARE @ENDCUSTOMER_NAME VARCHAR(100)
	DECLARE @PRODUCT_ID 	INT
	DECLARE @PRODUCT_NAME 	VARCHAR(50)
	DECLARE @MKTG_PART_NUMBER VARCHAR(50)
	DECLARE @ROW_ID 	INT
	DECLARE @MONTH 		INT
	DECLARE @YEAR 		INT
	DECLARE @QTY 		INT
	DECLARE @PRICE 		FLOAT
	DECLARE @CONFIDENCE_LEVEL FLOAT
	DECLARE @SALESPERSON_NAME VARCHAR(50)
	DECLARE @LEVEL_ORDER	INT
	DECLARE @PROJECTS_CURSOR  CURSOR
	DECLARE @IS_SHOW_PRICE_FIELD VARCHAR(10)
	DECLARE @USER_DEFINED1 VARCHAR(50)
	DECLARE @USER_DEFINED2 VARCHAR(50)
	DECLARE @USER_DEFINED3 VARCHAR(50)
	DECLARE @USER_DEFINED4 VARCHAR(50)
	DECLARE @ENDUSER_ID INT
	DECLARE @ENDUSER_NAME VARCHAR(100)	

	SET @PROJECTS_CURSOR = CURSOR FOR SELECT DISTINCT PS.PROJNAME, PS.PROJID, PS.CUSTID, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID=PS.CUSTID), C.Customer_ID, C.C_Name, P.Product_ID, P.Part_Number, P.P_Marketing_Part_Number, SP.SalesPerson_Name, FSPR.LEVELORDER,
							PS.ENDUSERID, EU.C_NAME AS ENDUSER_NAME
						FROM PROJSETUP PS, PROJPRODREL PPR, Product P, Customer C, SALES_PERSONS SP, FCSPERSONREL FSPR, Customer EU
						WHERE PS.SPERSONID = @SALESPERSON_ID AND
							PS.PROJID = PPR.PROJID AND
							PS.PROJDISABLED = 1 AND --ACTIVE PROJECT
							P.Product_ID = PPR.PRODID AND
							C.Customer_ID = ISNULL( PS.ODMID, PS.CUSTID ) AND
							EU.Customer_ID = ISNULL(PS.ENDUSERID, ISNULL( PS.ODMID, PS.CUSTID ) )  AND
							SP.SalesPerson_Id = PS.SPERSONID AND
							FSPR.SPERSONID = PS.SPERSONID ORDER BY C.C_Name, PROJNAME
							
	OPEN @PROJECTS_CURSOR
	FETCH NEXT FROM @PROJECTS_CURSOR INTO @PROJECT_NAME, @PROJECT_ID, @CUSTOMER_ID, @CUSTOMER_NAME, @ENDCUSTOMER_ID, @ENDCUSTOMER_NAME, @PRODUCT_ID, @PRODUCT_NAME, @MKTG_PART_NUMBER, @SALESPERSON_NAME, @LEVEL_ORDER, @ENDUSER_ID, @ENDUSER_NAME
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		SET @MONTH	= @START_MONTH
		SET @YEAR	= @START_YEAR
		SET @MONTH_COUNT= 0
		WHILE ( @MONTH_COUNT < @SHOW_NEXT )
		 BEGIN
			IF ( @MONTH = 12 )
			 BEGIN
				SET @MONTH = 0
				SET @YEAR  = @YEAR + 1
			 END
			SET @QTY		= 0
			SET @PRICE		= 0
			SET @CONFIDENCE_LEVEL 	= 0
			SET @ROW_ID 		= 0
			SELECT @ROW_ID = RowId, @QTY = Qty, @PRICE = Price, @CONFIDENCE_LEVEL = Confidence_level FROM FCENTRYMASTER WHERE SPersonId = @SALESPERSON_ID AND ProdId = @PRODUCT_ID AND ProjId = @PROJECT_ID AND Month = @MONTH AND Year  = @YEAR
			/* This part is Hard Coded for Beceem. Needs to be modified according to customer requirements for other customers */
			SELECT @USER_DEFINED1 = ENTRYVALUE FROM FCUENTRYVAL WHERE ENTRYNAME = 'Program' AND PROJID = @PROJECT_ID
			SELECT @USER_DEFINED2 = ENTRYVALUE FROM FCUENTRYVAL WHERE ENTRYNAME = 'Region' AND PROJID = @PROJECT_ID
			
			INSERT INTO @FN_RESULTS VALUES ( @PROJECT_ID, @PROJECT_NAME, @CUSTOMER_ID, @CUSTOMER_NAME, @ENDCUSTOMER_ID, @ENDCUSTOMER_NAME, @PRODUCT_ID, @PRODUCT_NAME, @MKTG_PART_NUMBER, @ROW_ID, @MONTH, @YEAR, @QTY, @PRICE, @CONFIDENCE_LEVEL, @SALESPERSON_NAME, @LEVEL_ORDER, 0, @USER_DEFINED1, @USER_DEFINED2, @USER_DEFINED3, @USER_DEFINED4, @ENDUSER_ID, @ENDUSER_NAME )
			SELECT @IS_SHOW_PRICE_FIELD = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'scalar.chiptrack.forecast.show.price'
			IF(@IS_SHOW_PRICE_FIELD IS NULL OR @IS_SHOW_PRICE_FIELD='true')
			BEGIN
				UPDATE @FN_RESULTS SET RUNNING_TOTAL = ( SELECT SUM(QTY*PRICE) FROM @FN_RESULTS WHERE MONTH = @MONTH AND YEAR = @YEAR ) WHERE MONTH = @MONTH AND YEAR = @YEAR
			END
			ELSE
			BEGIN
				UPDATE @FN_RESULTS SET RUNNING_TOTAL = ( SELECT SUM(QTY) FROM @FN_RESULTS WHERE MONTH = @MONTH AND YEAR = @YEAR ) WHERE MONTH = @MONTH AND YEAR = @YEAR
			END
			SET @MONTH = @MONTH + 1
			SET @MONTH_COUNT = @MONTH_COUNT + 1
		 END
		SET @PROJECT_ID 	= NULL
		SET @PROJECT_NAME 	= NULL
		SET @ENDCUSTOMER_ID 	= NULL
		SET @ENDCUSTOMER_NAME 	= NULL
		SET @PRODUCT_ID 	= NULL
		SET @PRODUCT_NAME 	= NULL
		SET @SALESPERSON_NAME	= NULL
		SET @LEVEL_ORDER	= NULL
		SET @USER_DEFINED1 = NULL
		SET @USER_DEFINED2 = NULL
		SET @USER_DEFINED3 = NULL
		SET @USER_DEFINED4 = NULL
		SET @ENDUSER_ID = -1
		SET @ENDUSER_NAME = NULL
 		FETCH NEXT FROM @PROJECTS_CURSOR INTO @PROJECT_NAME, @PROJECT_ID, @CUSTOMER_ID, @CUSTOMER_NAME, @ENDCUSTOMER_ID, @ENDCUSTOMER_NAME, @PRODUCT_ID, @PRODUCT_NAME, @MKTG_PART_NUMBER, @SALESPERSON_NAME, @LEVEL_ORDER, @ENDUSER_ID, @ENDUSER_NAME
	 END
	CLOSE @PROJECTS_CURSOR
	DEALLOCATE @PROJECTS_CURSOR
	RETURN
 END
